/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.moedik.model;

import com.moedik.classes.User;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Sammy
 */
public class UserModel extends Model {

    public UserModel() {
        super();
    }

    public int validate(String username, String password) {
        int userId = 0;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM `users` WHERE `users`.`username` = '" + username + "' AND `users`.`password` = MD5('" + password + "')");

            while (rs.next()) {
                userId = rs.getInt("id");
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return userId;
    }

    public User get(int id) {
        User user = null;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT `users`.`id` AS `id`, `users`.`username` AS `username`, `users`.`role_id` AS `role_id`, `roles`.`name` AS `role`, `users`.`name` AS `name`, `users`.`email` AS `email`, `users`.`address` AS `address`, `cities`.`name` AS `city`, `users`.`phone` AS `phone` FROM `users` JOIN `roles` JOIN `cities` WHERE `users`.`id` = " + id + " AND `users`.`role_id` = `roles`.`id` AND `users`.`city_id` = `cities`.`id`");

            while (rs.next()) {
                user = new User(rs.getInt("id"), rs.getString("username"), rs.getInt("role_id"), rs.getString("role"), rs.getString("name"), rs.getString("email"), rs.getString("address"), rs.getString("city"), rs.getString("phone"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return user;
    }
    
    public ArrayList<User> getAll() {
        ArrayList<User> users = new ArrayList<User>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT `users`.`id` AS `id`, `users`.`username` AS `username`, `users`.`role_id` AS `role_id`, `roles`.`name` AS `role`, `users`.`name` AS `name`, `users`.`email` AS `email`, `users`.`address` AS `address`, `cities`.`name` AS `city`, `users`.`phone` AS `phone` FROM `users` JOIN `roles` JOIN `cities` WHERE `users`.`role_id` = `roles`.`id` AND `users`.`city_id` = `cities`.`id` ORDER BY users.id");

            while (rs.next()) {
                User user = new User(rs.getInt("id"), rs.getString("username"), rs.getInt("role_id"), rs.getString("role"), rs.getString("name"), rs.getString("email"), rs.getString("address"), rs.getString("city"), rs.getString("phone"));
                users.add(user);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return users;
    }

    /***
     * fungsi signup, menambah record baru ke tabel users
     * output : last inserted id
     * 
     * @param username
     * @param password
     * @param role_id
     * @param name
     * @param email
     * @param address
     * @param city_id
     * @param phone
     * @return
     * @throws SQLException 
     */
    public int signup(String username, String password, int role_id, String name, String email, String address, int city_id, String phone) throws SQLException {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int key = 0;

        String query = "INSERT INTO users (username, password, role_id, name, email, address, city_id, phone) VALUES ('" + username + "', '" + password + "', " + role_id + ", '" + name + "', '" + email + "', '" + address + "', " + city_id + ", '" + phone + "')";
        stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        stmt.executeUpdate();
        rs = stmt.getGeneratedKeys();

        rs.next();
        key = rs.getInt(1);
        this.lm.log("user "+username+" telah ditambahkan");
        return key;
    }

    /***
     * melakukan validasi username, sudah ada di database atau belum
     * output : id record di tabel users yang memiliki username seperti pada parameter
     * 
     * @param username
     * @return 
     */
    public int validateUsername(String username) {
        int userId = 0;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM `users` WHERE `users`.`username` = '" + username + "'");

            while (rs.next()) {
                userId = rs.getInt("id");
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return userId;
    }

    /***
     * melakukan validasi email, sudah ada di database atau belum
     * output : id record di tabel users yang memiliki email seperti pada parameter
     * 
     * @param email
     * @return 
     */
    public int validateEmail(String email) {
        int userId = 0;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM `users` WHERE `users`.`email` = '" + email + "'");

            while (rs.next()) {
                userId = rs.getInt("id");
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return userId;
    }

    /***
     * melakukan validasi phone, sudah ada di database atau belum
     * output : id record di tabel users yang memiliki phone seperti pada parameter
     * 
     * @param phone
     * @return 
     */
    public int validatePhone(String phone) {
        int userId = 0;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM `users` WHERE `users`.`phone` = '" + phone + "'");

            while (rs.next()) {
                userId = rs.getInt("id");
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return userId;
    }

    public void update(int id, String name, String password, int roleId, String email, String address, int cityId, String phone) throws SQLException {
        PreparedStatement stmt = null;
        String query;
        if(!password.equals("")){
            query = "UPDATE users SET name = '" + name + "', password='" + password + "', email='" + email + "', address='" + address + "', city_id=" + cityId + ", role_id=" + roleId + ", phone='" + phone + "' WHERE id='" + id + "'";
        }else{
            query = "UPDATE users SET name = '" + name + "', email='" + email + "', address='" + address + "', city_id=" + cityId + ", role_id=" + roleId + ", phone='" + phone + "' WHERE id='" + id + "'";
        }
        stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
        stmt.executeUpdate();
        this.lm.log("user "+id+" telah diperbaharui");
    }
    
    public int delete(int id) {
        int rowCount = 0;
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "DELETE FROM `users` WHERE `users`.`id` = " + id;
            rowCount = stmt.executeUpdate(query);
            this.lm.log("user "+id+" telah dihapus");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return rowCount;
    }
    
}
